#! /usr/bin/env python3
# -*- coding:utf-8 -*-


import pymysql
import _md5

# 创建链接
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='rootroot',db='demo',charset='utf8')

# 创建游标  ; 默认返回的是 tulpe
# cursor = conn.cursor()
# 游标设置为字典类型 dict
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# # insert 返回受影响行数，执行多次
# insert_rows = cursor.executemany("insert into users(user_name,user_pass,user_desc) values(%s,%s,%s)",[('demo13','111111',''),('news23','123','lisi')])
# print(insert_rows)
# # 需要提交
# conn.commit();



# select 执行SQL，并返回受影响行数
# effect_row = cursor.execute('select * from users')
# print(effect_row)

# # 获取剩余结果的第一行数据
# row_1 = cursor.fetchone()  # 返回的是一个tuple
# print(row_1)
# print(type(row_1))
#
# # 获取剩余结果的前n行数据
# row_n = cursor.fetchmany(3)
# print(row_n)
#
# #获取剩余结果的全部数据
# row_all = cursor.fetchall()
# print(row_all)

# for v in row_all:
#     print(v)
#     print(v["user_name"])


# # update 返回受影响行数
# update_row = cursor.execute("update users set user_pass='zhsang' where id = %s",(1,))
# print(update_row)
# # 需要提交
# conn.commit();


# 调用存储过程
## 调用无参存储过程
# cursor.callproc('p2') # 等价于 cursor.execute("call p2()")

## 调用有参存储过程
# cursor.callproc('p1', args=(1,2,3,4))
## 获取执行完存储过程的参数，参数@ 开头
# cursor.execute("select @p1,@_p2")
# # 返回#{u'@p1': 22, u'@_p2': 1}
# rows = cursor.fetchall()
# print(rows)





cursor.close()
conn.close()

# h获取自增ID  好像有问题，插入2条时，返回的是第一条的id
# new_id = cursor.lastrowid
# print(new_id)
